﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using Simple.Services.System.QHSEUser.Models;
using Simple.Services.System.QHSEViolationCategory.Models;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using Simple.Services.System.QHSEDanger.Models.AnalysisInput;

namespace Simple.Services;

public class DangerKeyWorkAnalysisService
{
    private readonly CacheService _cacheService;
    private readonly SimpleDbContext _context;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEDangerCategoryService _dangerCategoryService;
    public DangerKeyWorkAnalysisService(SimpleDbContext context, QHSEDictionaryService dictionaryService, QHSEDangerCategoryService dangerCategoryService, CacheService cacheService)
    {
        _context = context;
        _dictionaryService = dictionaryService;
        _dangerCategoryService = dangerCategoryService;
        _cacheService = cacheService;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<List<DangerAnalysisResult>> KeyWorkAnalysisAsync(DangerKeyWorkInput input)
    {
        var cachedResult = await _cacheService.GetKeyWorkKeyWorkAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.WorkSpeciality.Count != 0 && input.KeyWorks.Count != 0
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result; // 如果未找到对应组织，直接返回结果

            var escapedNamePath = EscapeLikePattern(org.NamePath);

            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            // 使用索引的前缀列
            var dangerCounts = await query
                .GroupBy(d => new { d.WorkSpecialty, d.WorkType })
                .Select(g => new
                {
                    WorkSpecialty = g.Key.WorkSpecialty,
                    WorkType = g.Key.WorkType,
                    Count = g.Count()
                })
                .ToListAsync();

            var keyWorkDict = input.KeyWorks.ToDictionary(keyWork => keyWork, keyWork => keyWork.Split('-'));

            result = keyWorkDict.Select(keyWork =>
            {
                var keyWorkName = keyWork.Key;
                var specialty = keyWork.Value[0];
                var workType = keyWork.Value[1];
                var count = dangerCounts.Where(dc => dc.WorkSpecialty == specialty && dc.WorkType == workType)
                                        .Sum(dc => dc.Count);
                return new DangerAnalysisResult
                {
                    Name = keyWorkName,
                    Value = count
                };
            }).OrderByDescending(res => res.Value).ToList();
        }

        await _cacheService.SetKeyWorkKeyWorkAnalysisAsync(input, result);
        return result;
    }


    public async Task<CompareDangerAnalysisResult> DangerTypeAnalysisAsync(DangerKeyWorkInput input)
    {
        var cachedResult = await _cacheService.GetKeyWorkDangerTypeAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.WorkSpeciality.Count != 0 && input.KeyWorks.Count != 0
           && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");

            List<Object> vio = new List<Object> { "隐患级别" };
            vio.AddRange(dangerTypes.Select(violationType => violationType.Item));
            result.Item.Add(vio);

            var dangerCounts = await query
                .GroupBy(d => new { d.WorkSpecialty, d.WorkType, d.DangerType })
                .Select(g => new
                {
                    g.Key.WorkSpecialty,
                    g.Key.WorkType,
                    g.Key.DangerType,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var keyWork in input.KeyWorks)
            {
                List<Object> violation = new List<Object> { keyWork };
                var keyWorkSplit = keyWork.Split('-');
                var specialty = keyWorkSplit[0];
                var workType = keyWorkSplit[1];

                foreach (var dangerType in dangerTypes)
                {
                    var count = dangerCounts.FirstOrDefault(vc => vc.WorkSpecialty == specialty && vc.WorkType == workType && vc.DangerType == dangerType.Item)?.Count ?? 0;
                    violation.Add(count);
                }

                result.Item.Add(violation);
            }
        }

        await _cacheService.SetKeyWorkDangerTypeAnalysisAsync(input, result);
        return result;
    }

    public async Task<CompareDangerAnalysisResult> DangerStatusAnalysisAsync(DangerKeyWorkInput input)
    {
        var cachedResult = await _cacheService.GetKeyWorkDangerStatusAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.KeyWorks.Count != 0
           && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerCounts = await query
                .GroupBy(d => new { d.WorkSpecialty, d.WorkType, d.Status })
                .Select(g => new
                {
                    g.Key.WorkSpecialty,
                    g.Key.WorkType,
                    g.Key.Status,
                    Count = g.Count()
                })
                .ToListAsync();

            List<Object> vio = new List<Object>
        {
            "隐患整改情况",
            "全部",
            "已整改",
            "未整改"
        };
            result.Item.Add(vio);

            foreach (var keyWork in input.KeyWorks)
            {
                List<Object> violation = new List<Object> { keyWork };
                var keyWorkSplit = keyWork.Split('-');
                var specialty = keyWorkSplit[0];
                var workType = keyWorkSplit[1];

                var total = dangerCounts.Where(dc => dc.WorkSpecialty == specialty && dc.WorkType == workType)
                                        .Sum(dc => dc.Count);
                var resolved = dangerCounts.FirstOrDefault(dc => dc.WorkSpecialty == specialty && dc.WorkType == workType && dc.Status == "已整改")?.Count ?? 0;
                var unresolved = total - resolved;

                violation.Add(total);
                violation.Add(resolved);
                violation.Add(unresolved);

                result.Item.Add(violation);
            }
        }

        await _cacheService.SetKeyWorkDangerStatusAnalysisAsync(input, result);
        return result;
    }

    public async Task<CompareDangerAnalysisResult> DangerCategoryAnalysisAsync(DangerKeyWorkInput input, string name)
    {
        var cachedResult = await _cacheService.GetKeyWorkDangerCategoryAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var violationCategories = await _dangerCategoryService.GetSon(name);
            if (name == "不安全因素")
            {
                name = "隐患类别";
            }

            var violationCategoryChildren = new Dictionary<string, List<string>>();
            foreach (var category in violationCategories)
            {
                var children = await _dangerCategoryService.GetChildren(category);
                violationCategoryChildren[category] = children;
            }

            List<Object> vio = new List<Object> { name };
            vio.AddRange(violationCategories);
            result.Item.Add(vio);

            var dangerCounts = await query
                .GroupBy(d => new { d.WorkSpecialty, d.WorkType, d.DangerCategory })
                .Select(g => new
                {
                    g.Key.WorkSpecialty,
                    g.Key.WorkType,
                    g.Key.DangerCategory,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var keyWork in input.KeyWorks)
            {
                List<Object> violation = new List<Object> { keyWork };
                var keyWorkSplit = keyWork.Split('-');
                var specialty = keyWorkSplit[0];
                var workType = keyWorkSplit[1];

                foreach (var category in violationCategories)
                {
                    var children = violationCategoryChildren[category];
                    var count = dangerCounts.Where(dc => dc.WorkSpecialty == specialty && dc.WorkType == workType && children.Contains(dc.DangerCategory))
                                            .Sum(dc => dc.Count);
                    violation.Add(count);
                }

                result.Item.Add(violation);
            }
        }

        await _cacheService.SetKeyWorkDangerCategoryAnalysisAsync(input, result);
        return result;
    }


    private async Task<List<int>> GetViolationCountsAsync(IQueryable<QHSESysDanger> query, string keyWork, List<string> violationCategories)
    {
        var violationCounts = new List<int>();

        foreach (var violationCategory in violationCategories)
        {
            var children = await _dangerCategoryService.GetChildren(violationCategory);
            var count = await query
                .Where(v => keyWork == v.WorkSpecialty + "-" + v.WorkType && children.Contains(v.DangerCategory))
                .CountAsync();
            violationCounts.Add(count);
        }

        return violationCounts;
    }
}
